/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package cannes.dao.implement;

import cannes.bean.planning.Projection;
import cannes.connexion.ConnexionBDD;
import cannes.dao.DAO;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;
/**
 *
 * @author Pierre
 */
public class ProjectionDAO extends DAO<Projection> {

    public ProjectionDAO(Connection conn) {
        super(conn);
    }

    @Override
    public boolean create(Projection obj) {
        
        java.sql.Timestamp ts = new java.sql.Timestamp(obj.getHorraire().getTime());
        String INSERT_RECORD = "INSERT INTO projection VALUES(?,?,?,?,?)";
        
        try
        {
            PreparedStatement pstmt = connect.prepareStatement(INSERT_RECORD);
            pstmt.setInt(1, obj.getId());
            pstmt.setInt(2, obj.getPlanning());
            pstmt.setInt(3, obj.getSalle());
            pstmt.setInt(4, obj.getFilm());
            pstmt.setTimestamp(5, ts);
            
            pstmt.executeUpdate();
            return true;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
    }

    @Override
    public boolean update(Projection obj) {
        try
        {
        this.connect.createStatement(
                ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY).executeQuery(
                "UPDATE projection SET planningid =" + obj.getPlanning() +",filmid = " + obj.getFilm() + ",salleid =" + obj.getSalle() +",horraire =" + obj.getHorraire() + "WHERE projectionid = " + obj.getId());
        } catch (SQLException e) {
            e.printStackTrace();

        }
        return false;
    }

    @Override
    public boolean delete(Projection obj) {
        try {
            ResultSet result = this.connect.createStatement(
                    ResultSet.TYPE_SCROLL_INSENSITIVE,
                    ResultSet.CONCUR_READ_ONLY).executeQuery(
                    "DELETE FROM projection WHERE projection = " + obj.getId());
            result.rowDeleted();

        } catch (SQLException e) {
            e.printStackTrace();
        }

        return false;
    }

    @Override
    public int next() {
        int i = 0;
        try {
            ResultSet result = this.connect.createStatement(
                    ResultSet.TYPE_SCROLL_INSENSITIVE,
                    ResultSet.CONCUR_READ_ONLY).executeQuery(
                    "SELECT max(projectionid) from projection");
            if (result.first()) i = result.getInt(1)+1;

        } catch (SQLException e) {
            e.printStackTrace();
        }
        return i;
    }



    @Override
    public Projection find(int id) {
        Projection projection = new Projection();

        try {
            ResultSet res = this.connect.createStatement(
                    ResultSet.TYPE_SCROLL_INSENSITIVE,
                    ResultSet.CONCUR_READ_ONLY).executeQuery(
                    "select * from projection WHERE projectionid = " + id);

            if (res.first()) {
                projection = new Projection(res.getInt("projectionid"),res.getDate("horraire"),
                        res.getInt("planningid"), res.getInt("salleid"),res.getInt("salleid"));

                InvitationDAO invitDAO = new InvitationDAO(this.connect);

                res = this.connect.createStatement().executeQuery(
                    "SELECT invitationid FROM invitation WHERE projectionid = " + id);

                while (res.next()) {
                    projection.addInvitation(invitDAO.find(res.getInt("invitationid")));
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return projection;

    }

    @Override
    public ArrayList<Projection> findAll() {
        ArrayList<Projection> listeProjection = new ArrayList<Projection>();
        Projection projection;
        try {
            ResultSet res = this.connect.createStatement(
                    ResultSet.TYPE_SCROLL_INSENSITIVE,
                    ResultSet.CONCUR_READ_ONLY).executeQuery(
                    "select * from projection ");
            
            InvitationDAO invitDAO = new InvitationDAO(this.connect);
            ResultSet resInvit;
            
            while(res.next()) {
                projection = new Projection(res.getInt("projectionid"),res.getDate("horraire"),
                        res.getInt("planningid"), res.getInt("salleid"),res.getInt("salleid"));
                
                System.out.println(res.isLast());

                resInvit = this.connect.createStatement().executeQuery(
                    "SELECT invitationid FROM invitation WHERE projectionid = " + projection.getId());

                while (resInvit.next()) {
                    projection.addInvitation(invitDAO.find(resInvit.getInt("invitation")));
                }
                
                listeProjection.add(projection);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        
        return listeProjection;
        
    }
    
    public ArrayList<Projection> findByFilmId(int id){
        ArrayList<Projection> list = new ArrayList<Projection>();
        Projection projection;
        try {
            ResultSet res = this.connect.createStatement(
                    ResultSet.TYPE_SCROLL_INSENSITIVE,
                    ResultSet.CONCUR_READ_ONLY).executeQuery(
                    "select * from projection where filmid = " + id);
            
            InvitationDAO invitDAO = new InvitationDAO(this.connect);
            ResultSet resInvit;
            
            while(res.next()) {
                projection = new Projection(res.getInt("projectionid"),res.getDate("horraire"),
                        res.getInt("planningid"), res.getInt("salleid"),res.getInt("salleid"));
                
                System.out.println(res.isLast());

                resInvit = this.connect.createStatement().executeQuery(
                    "SELECT invitationid FROM invitation WHERE projectionid = " + projection.getId());

                while (resInvit.next()) {
                    projection.addInvitation(invitDAO.find(resInvit.getInt("invitation")));
                }
                
                list.add(projection);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }      
        
        return list;
    }
    
        public ArrayList<Projection> findBySalleId(int id){
        ArrayList<Projection> list = new ArrayList<Projection>();
        Projection projection;
        try {
            ResultSet res = this.connect.createStatement(
                    ResultSet.TYPE_SCROLL_INSENSITIVE,
                    ResultSet.CONCUR_READ_ONLY).executeQuery(
                    "select * from projection where salleid = " + id);
            
            InvitationDAO invitDAO = new InvitationDAO(this.connect);
            ResultSet resInvit;
            
            while(res.next()) {
                projection = new Projection(res.getInt("projectionid"),res.getDate("horraire"),
                        res.getInt("planningid"), res.getInt("salleid"),res.getInt("salleid"));
                
                System.out.println(res.isLast());

                resInvit = this.connect.createStatement().executeQuery(
                    "SELECT invitationid FROM invitation WHERE projectionid = " + projection.getId());

                while (resInvit.next()) {
                    projection.addInvitation(invitDAO.find(resInvit.getInt("invitation")));
                }
                
                list.add(projection);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }      
        
        return list;
    }
}
